﻿--HEADER Drop [FK_Answer_User], [FK_QuestionToUser_User] foreign keys; 

--Drop foreign key FK_Answer_User
ALTER TABLE [UDS].[Answer] DROP CONSTRAINT [FK_Answer_User]

--Drop foreign key FK_QuestionToUser_User
ALTER TABLE [UDS].[QuestionToUser] DROP CONSTRAINT [FK_QuestionToUser_User]


exec sp_rename N'UDS.PK_IdeaAnswer', N'PK_AnswerAtIdea', N'OBJECT'
exec sp_rename N'UDS.FK_IdeaAnswer_Answer', N'FK_AnswerAtIdea_Answer', N'OBJECT'
exec sp_rename N'UDS.IdeaAnswer', 'AnswerAtIdea'
GO

ALTER TABLE [UDS].[TradeToIdea]
ALTER COLUMN CreatorCode TINYINT NOT NULL
GO

--Create UDS.Tag table
CREATE TABLE [UDS].[Tag]
(
	[Id] INT NOT NULL  IDENTITY, 
    [Name] NVARCHAR(50) NOT NULL,
	 [OwnerUserId] INT NOT NULL, 
    CONSTRAINT [PK_Tag] PRIMARY KEY ([Id])
)

GO

CREATE TABLE [UDS].[TagToIdea]
(
	[TagId] INT NOT NULL , 
    [IdeaId] UNIQUEIDENTIFIER NOT NULL, 
    CONSTRAINT [PK_TagToIdea] PRIMARY KEY ([TagId], [IdeaId]),    
)

GO

ALTER TABLE [UDS].[TagToIdea] ADD  CONSTRAINT [FK_TagToIdea_Tag] FOREIGN KEY([TagId])
REFERENCES [UDS].[Tag] ([Id])
GO

ALTER TABLE [UDS].[TagToIdea]  ADD  CONSTRAINT [FK_TagToIdea_Idea] FOREIGN KEY([IdeaId])
REFERENCES [UDS].[Idea] ([Id])
GO

--Copy tag info
INSERT UDS.Tag (Name, OwnerUserId)
SELECT Name, OwnerUserId
FROM iPerform.Tag

INSERT UDS.TagToIdea (TagId, IdeaId)
SELECT TagId, IdeaId
FROM iPerform.IdeaToTag